# -*- coding: utf-8 -*-
# autor: Rui-6047, Pedro-11240
# data: 25 de Outubro de 2013
# obs: 1º Parte do Trabalho de LP

from sqlalchemy import *

distritos = [[u'Açores'], 
			[u'Faro', u'Algarve'],
			[u'Aveiro'], 
			[u'Coimbra'], 
			[u'Évora'], 
			[u'Lisboa', u'Náutica', u'Estoril'], 
			[u'Porto'],
			[u'Madeira'], 
			[u'Beja'], 
			[u'Bragança'], 
			[u'Castelo Branco', u'Beira Interior'],
			[u'Guarda'],
			[u'Leiria'],
			[u'Portalegre'],
			[u'Santarém', u'Tomar'],
			[u'Setúbal'],
			[u'Viana do Castelo'],
			[u'Viseu'],
			[u'Braga', u'Minho', u'Cávado e do Ave'],
			[u'Trás-os-Montes e Alto Douro']]

allInstitutions = []
postedNumberPerInstitutions = []
freeNumberPerInstitutions = []
postedNumberPerDistrict = []
freeNumberPerDistrict = []
studentPercent = []
studentPermil = []

def getData_conn():
	db = create_engine('sqlite:///database.db')
	db.echo = True
	metadata = MetaData(db)
	data = Table('Data', metadata, autoload=True)
	return data

########################################################################
## Consultas a Base de Dados - Guarda Todas as instituicoes num array
########################################################################
def getAllInstitutions(allInstitutions):
	data = getData_conn()
	s = select([data.c.Nome_instituicao]).distinct()
	rs = s.execute()
	for row in rs:
		allInstitutions.append(row.Nome_instituicao)
	pass
	return allInstitutions
pass
	
########################################################################
## Consultas a Base de Dados - Guarda o nº de colocados em todas as 
## instituições
########################################################################
def gettotalStudentes():
	data = getData_conn()
	totalStudents = 0
	s = select([func.sum(data.c.Colocados)])
	rs = s.execute()
	for row in rs:
			totalStudents = row[0]
	pass
	return totalStudents
pass
########################################################################
## Consultas a Base de Dados - Guarda o nº de colocados por instituicao
########################################################################
def getPostedNumberPerInstitutions(allInstitutions, 
									postedNumberPerInstitutions):
	data = getData_conn()
	for index, item in enumerate(allInstitutions):
		s = select([func.sum(data.c.Colocados)], 
									data.c.Nome_instituicao == item)
		rs = s.execute()
		for row in rs:
			postedNumberPerInstitutions.append(row[0])
		pass
	pass
	return postedNumberPerInstitutions
pass
########################################################################
## Consultas a Base de Dados - Guarda o nº de vagas por instituicao
########################################################################
def getFreeNumberPerInstitutions(allInstitutions, 
									freeNumberPerInstitutions):
	data = getData_conn()
	for index, item in enumerate(allInstitutions):
		s = select([func.sum(data.c.Vagas_sobrantes)], 
									data.c.Nome_instituicao == item)
		rs = s.execute()
		for row in rs:
			freeNumberPerInstitutions.append(row[0])
		pass
	pass
	return freeNumberPerInstitutions
pass
########################################################################
## Consultas a Base de Dados - Guarda o nº de colocados por Distrito
########################################################################
def getpostedNumberPerDistrict(distritos, postedNumberPerDistrict):
	data = getData_conn()
	for dis in distritos:
		totalDistrito = 0
		for local in dis:
			colocados = 0
			s = select([func.sum(data.c.Colocados)], 
					data.c.Nome_instituicao.like('% ' + local + ' %'))
			rs = s.execute()
			for row in rs:
				colocados = row[0]
				if colocados == None:
					colocados = 0
				pass
				totalDistrito = totalDistrito + colocados
			pass
		pass
		postedNumberPerDistrict.append(totalDistrito)
	pass
	return postedNumberPerDistrict
pass
########################################################################
## Consultas a Base de Dados - Guarda o nº de vagas por Distrito
########################################################################
def getfreeNumberPerDistrict(distritos, freeNumberPerDistrict):
	data = getData_conn()
	for dis in distritos:
		totalDistrito = 0
		for local in dis:
			vagas = 0
			s = select([func.sum(data.c.Vagas_sobrantes)], 
					data.c.Nome_instituicao.like('% ' + local + ' %'))
			rs = s.execute()
			for row in rs:
				vagas = row[0]
				if vagas == None:
					vagas = 0
				pass
				totalDistrito = totalDistrito + vagas
			pass
		pass
		freeNumberPerDistrict.append(totalDistrito)
	pass
	return postedNumberPerDistrict
pass
########################################################################
## Consultas a Base de Dados - percentagem de alunos por instituicao
########################################################################
def getStudentPercent(allInstitutions, 
						postedNumberPerInstitutions, 
						studentPercent, 
						totalStudents):
	data = getData_conn()
	for index, item in enumerate(allInstitutions):
		percent = (float(postedNumberPerInstitutions[index])/
													totalStudents)*100
		studentPercent.append(percent)
	pass
	return studentPercent
pass
########################################################################
## Consultas a Base de Dados - perleminagem de alunos por instituicao
########################################################################
def getStudentPermil(distritos, 
						postedNumberPerDistrict, 
						studentPermil, 
						totalStudents):
	data = getData_conn()
	for index, item in enumerate(distritos):
		permil = (float(postedNumberPerDistrict[index])/
													totalStudents)*1000
		studentPermil.append(permil)
	pass
	return studentPermil
pass



